﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Text.RegularExpressions;
using Microsoft.Office.Tools.Excel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;


namespace ABDLight
{
    public partial class frmExport : Form
    {
        //public static readonly ILog log = LogManager.GetLogger(typeof(frmExport));

            
        public frmExport()
        {
            //log.Info("== Инициализация формы frmExport ==");
            InitializeComponent();
            this.cmbSeparator.SelectedIndex = 0;
            this.cmbType.SelectedIndex = 0;
            this.cmbCode.SelectedIndex = 0;
            this.ClientSize = new System.Drawing.Size(SystemInformation.PrimaryMonitorSize.Width / 2, SystemInformation.PrimaryMonitorSize.Height / 4 * 3);
        }



     
         private void frmExport_Load(object sender, EventArgs e)
        {
            aejw.cIni oIni = new aejw.cIni(Application.StartupPath + "\\settings.ini");
            
            if (((frmMine)this.Owner).dtMain.Columns.Count == 0) return;
            for (int i = 0; i < ((frmMine)this.Owner).dtMain.Columns.Count; i++)
            {
                grdColumn.Rows.Add(1);
                grdColumn.Rows[i].Cells[0].Value = ((frmMine)this.Owner).dtMain.Columns[i].Caption;
                grdColumn.Rows[i].Cells[1].Value = ((frmMine)this.Owner).dtMain.Columns[i].ColumnName;

                /*
                string isCollumns2Export = oIni.ReadValue( "Collumns2Export", ((frmMine)this.Owner).dtMain.Columns[i].Caption, "true");

                if (isCollumns2Export.ToLower() == "true")
                {
                    trFields.Nodes.Add(((frmMine)this.Owner).dtMain.Columns[i].ColumnName, ((frmMine)this.Owner).dtMain.Columns[i].Caption);
                } 
                 */
            }
            grdColumn.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            grdColumn.SelectAll();
            for (int i = 0; i < grdColumn.SelectedRows.Count; i++)
            {
                oIni.WriteValue("Collumns2Export", grdColumn.SelectedRows[i].Cells[0].Value.ToString(), "true");
                trFields.Nodes.Add(grdColumn.SelectedRows[i].Cells[1].Value.ToString(), grdColumn.SelectedRows[i].Cells[0].Value.ToString());
            }
            DelAllFromExport_Click(null, null);
        }

         private void btnAddTop_Click(object sender, EventArgs e)
         {
             aejw.cIni oIni = new aejw.cIni(Application.StartupPath + "\\settings.ini");

             if (grdColumn.SelectedRows.Count == 0) {

                 MessageBox.Show("Не выбрано ни одного поля!", "Внимание",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Warning);

                 return;
             }
             for (int i = 0; i < grdColumn.SelectedRows.Count; i++)
             {
                 if (oIni.ReadValue("Collumns2Export", grdColumn.SelectedRows[i].Cells[0].Value.ToString()) == "false")
                 { 
                    oIni.WriteValue("Collumns2Export", grdColumn.SelectedRows[i].Cells[0].Value.ToString(), "true");
                    trFields.Nodes.Add(grdColumn.SelectedRows[i].Cells[1].Value.ToString(), grdColumn.SelectedRows[i].Cells[0].Value.ToString());
                 }

             }
             int CurSelRow = grdColumn.SelectedRows[0].Index;
             grdColumn.ClearSelection();
             if (CurSelRow < grdColumn.Rows.Count - 1)
                grdColumn.Rows[CurSelRow + 1].Selected = true;
             else
                 grdColumn.Rows[CurSelRow].Selected = true;


         }

         private void btnAddToEnd_Click(object sender, EventArgs e)
         {
            
             if (grdColumn.SelectedRows.Count == 0)
             {

                 MessageBox.Show("Не выбрано ни одного поля!", "Внимание",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Warning);

                 return;
             }

             if (trFields.SelectedNode == null)
             {

                 MessageBox.Show("Не выбрано ведущее поле!", "Внимание",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Warning);

                 return;
             }
             
             for (int i = 0; i < grdColumn.SelectedRows.Count; i++)
             trFields.SelectedNode.Nodes.Add(grdColumn.SelectedRows[i].Cells[1].Value.ToString(), grdColumn.SelectedRows[i].Cells[0].Value.ToString());
         }

         private void btnDel_Click(object sender, EventArgs e)
         {
             

             if (trFields.SelectedNode != null)
             {
                 aejw.cIni oIni = new aejw.cIni(Application.StartupPath + "\\settings.ini");
                 //trFields.Nodes.Remove(trFields.SelectedNode);
                 //oIni.WriteValue("Collumns2Export", trFields.SelectedNode.Text, "false");

                 oIni.WriteValue("Collumns2Export", trFields.SelectedNode.Text, "false");
                 trFields.Nodes.Remove(trFields.SelectedNode);  
             }

            
         }

         private void btnOpen_Click(object sender, EventArgs e)
         {
            
             SaveFileDialog dlg = new SaveFileDialog();

             dlg.AddExtension = true;

             if (cmbType.SelectedIndex == 0)
                 dlg.Filter = "(*.xlsx)|*.xlsx";
             if (cmbType.SelectedIndex == 1)
                 dlg.Filter = "(*.xls)|*.xls";
             if (cmbType.SelectedIndex == 2)
                 dlg.Filter = "(*.csv)|*.csv|(*.txt)|*.txt";
             dlg.FilterIndex = 1;
             dlg.RestoreDirectory = true;
             dlg.AutoUpgradeEnabled = true;
             edFile.Text = dlg.FileName;
             if (dlg.ShowDialog() == DialogResult.OK)
             {

                 var FileToSave = File.Create(dlg.FileName);
                 FileToSave.Close();
                 edFile.Text = dlg.FileName;
             }


         }


         private List<string>  ObxodDereva(TreeNode a)
         {

             List<string> columns=new List<string>();
             if (a.Nodes != null)
             {
                 foreach (TreeNode childNode in a.Nodes) 
                 {
                     a = childNode; 
                     columns.Add(a.Name);
                   List<string> ccol=   ObxodDereva(a);
                   columns.AddRange(ccol);
                 }

             }

             return columns;
         }

         private void btnExport_Click(object sender, EventArgs e)
         {
             StatysLabel.Visible = true;
             DataTable dt = new DataTable();
             DataView dv = new DataView(((frmMine)this.Owner).dtMain);
             dv.RowFilter = ((frmMine)this.Owner).cfg.Filter;
             dt = dv.ToTable();



             if (File.Exists(edFile.Text))
             {
                 File.Delete(edFile.Text);
             }
             else
             {
                 /*
                 MessageBox.Show("Выберите файл для сохранения!", "Внимание",
                                                  MessageBoxButtons.OK,
                                                  MessageBoxIcon.Warning);
                 return;
                  * */

                 btnOpen_Click(null, null);
                 if (File.Exists(edFile.Text))
                    btnExport_Click(null, null);
                 return;

             }

             if (cmbType.SelectedIndex == 0 || cmbType.SelectedIndex == 1)
             {
                 LoadExcel(edFile.Text, dt,"Sheet1", cmbType.SelectedIndex);
             }
             else
             {
                 aejw.cIni oIni = new aejw.cIni(Application.StartupPath + "\\settings.ini");

                 Encoding enc = Encoding.GetEncoding(cmbCode.Text);
                 using (StreamWriter sw = new StreamWriter(edFile.Text, false, enc))
                 {
                     string line = "";
                     int ExpColNum = 0;
                     bool isNeed;
                     foreach (DataColumn col in dt.Columns)
                     {
                         ExpColNum += 1;
                         isNeed = false;
                         foreach (TreeNode node in trFields.Nodes)
                         {
                             if (node.Name.ToString() == col.ColumnName)
                             {
                                 isNeed = true;
                             }

                         }

                         if (isNeed)
                         {
                             if (chbTrans.Checked)
                                 line += Translit.TranslitToLatin(col.Caption, 1) + cmbSeparator.Text;
                             else
                                 line += col.Caption + cmbSeparator.Text;
                             oIni.WriteValue("Collumns2Export", col.Caption, "true");
                         }
                         else
                         {
                             oIni.WriteValue("Collumns2Export", col.Caption, "false");
                         }
                     }
                     line = Regex.Replace(line, cmbSeparator.Text + "$", "", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                     sw.WriteLine(line);
                     line = "";
                     int ExpRowNumGood = 0;
                     int ExpRowNun = 0;

                     foreach (DataRow Row in dt.Rows)
                     {
                         // Пересчет строк
                         for (int i = 0; i < dt.Columns.Count; i++)
                         {
                             isNeed = false;
                             foreach (TreeNode node in trFields.Nodes)
                             {
                                 if (node.Name.ToString() == dt.Columns[i].ColumnName)
                                 {
                                     isNeed = true;
                                 }
                             }

                             if (isNeed)
                             {
                                 if (frmClean.cCfg.chKov)
                                 {
                                     if (chbTrans.Checked)
                                         line = line + '"' + Translit.TranslitToLatin(Row.ItemArray[i].ToString().Replace(cmbSeparator.Text, " "), 1) + '"' + cmbSeparator.Text;
                                     else
                                         line = line + '"' + Row.ItemArray[i].ToString().Replace(cmbSeparator.Text, " ") + '"' + cmbSeparator.Text;
                                 }
                                 else
                                 {
                                     if (chbTrans.Checked)
                                         line = line + Translit.TranslitToLatin(Row.ItemArray[i].ToString().Replace(cmbSeparator.Text, " "), 1) + cmbSeparator.Text;
                                     else
                                         line = line + Row.ItemArray[i].ToString().Replace(cmbSeparator.Text, " ") + cmbSeparator.Text;
                                 }
                             }
                         }
                         line = Regex.Replace(line, cmbSeparator.Text + "$", "", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                         ExpRowNumGood = ExpRowNumGood + 1;
                         sw.WriteLine(line);
                         line = "";

                     };
                 }
             }

             MessageBox.Show("В файл " + edFile.Text + " успешно сохранено " + dt.Rows.Count.ToString() + " записей", "Внимание",
                                                  MessageBoxButtons.OK,
                                                  MessageBoxIcon.Information);

             StatysLabel.Visible = false;

             
         }


         private  void LoadExcel(string FileName = "test.xlsx", DataTable dt = null, string SheetName = "Sheet1", int ExType = 0)
         {
             if (dt == null) { return; }

             IWorkbook workbook;
             if(ExType == 0)
                 workbook = new XSSFWorkbook();
             else
                 workbook = new HSSFWorkbook();

             ISheet worksheet = workbook.CreateSheet(SheetName);


             IRow row = worksheet.CreateRow(0);
             DataRow dt_row = dt.Rows[0];

             int ExpColNum = 0;
             bool isNeed;
             foreach (DataColumn col in dt.Columns)
             {
                 isNeed = false;
                 foreach (TreeNode node in trFields.Nodes)
                 {
                     if (node.Name.ToString() == col.ColumnName)
                     {
                         isNeed = true;
                     }

                 }

                 if (isNeed)
                 {
                     ICell Cell = row.CreateCell(ExpColNum);
                     if (chbTrans.Checked)
                         Cell.SetCellValue(Translit.TranslitToLatin(col.Caption, 1));
                     else
                         Cell.SetCellValue(col.Caption);
                     ExpColNum += 1;
                     //line += col.Caption + cmbSeparator.Text;
                 }

             }

             int ExpRowNum = 0;
             int CurColInd = 0;
             foreach (DataRow Row in dt.Rows)
             {
                 ExpColNum = 0;
                 CurColInd = 0;
                 row = worksheet.CreateRow(ExpRowNum + 1);
                 dt_row = dt.Rows[ExpRowNum];
                 // Пересчет строк
                 foreach (DataColumn col in dt.Columns)
                 {
                     isNeed = false;
                     foreach (TreeNode node in trFields.Nodes)
                     {
                         if (node.Name.ToString() == col.ColumnName)
                         {
                             isNeed = true;
                         }

                     }

                     if (isNeed)
                     {
                         try
                         {
                             ICell Cell = row.CreateCell(ExpColNum);
                             if (!string.IsNullOrEmpty(dt_row[CurColInd].ToString()))
                             {
                                 if (chbTrans.Checked)
                                    Cell.SetCellValue(Translit.TranslitToLatin(dt_row[CurColInd].ToString(),1));
                                 else
                                     Cell.SetCellValue(dt_row[CurColInd].ToString());
                             }
                             else
                             {
                                 Cell.SetCellValue("");
                             }
                            ExpColNum += 1;
                         }
                         catch (Exception ex)
                         {
                         }
                     }
                     CurColInd += 1;
                 }
                 ExpRowNum += 1;
             }

             
             /*
             for (int colnum = 0; colnum < dt.Columns.Count; colnum++)
             {
                 //if (dt.Columns[colnum].ColumnName == "rowid") { continue; }
                 //if (dt.Columns[colnum].ColumnName == "c_file_id") { continue; }

                 ICell Cell = row.CreateCell(colnum);
                 Cell.SetCellValue(dt.Columns[colnum].ColumnName);
             }
             
             for (int rownum = 0; rownum < dt.Rows.Count; rownum++)
             {
                 row = worksheet.CreateRow(rownum + 1);
                 dt_row = dt.Rows[rownum];
                 for (int celnum = 0; celnum < dt.Columns.Count; celnum++)
                 {
                     try
                     {
                         //if (dt_row[celnum] == dt_row["rowid"]) { continue; }
                         //if (dt_row[celnum] == dt_row["c_file_id"]) { continue; }
                         ICell Cell = row.CreateCell(celnum);
                         if (!string.IsNullOrEmpty(dt_row[celnum].ToString()))
                         {
                             Cell.SetCellValue(dt_row[celnum].ToString());
                         }
                         else
                         {
                             Cell.SetCellValue("");
                         }
                     }
                     catch (Exception ex)
                     {
                     }
                 }
             }
*/
             FileStream sw = File.Create(FileName);
             workbook.Write(sw);
             sw.Close();
         }
            






         private void edFile_TextChanged(object sender, EventArgs e)
         {

         }

         private void trFields_AfterSelect(object sender, TreeViewEventArgs e)
         {
             /*
             if (trFields.SelectedNode != null)
             {
                 aejw.cIni oIni = new aejw.cIni(Application.StartupPath + "\\settings.ini");
                 trFields.Nodes.Remove(trFields.SelectedNode);
                 oIni.WriteValue("Collumns2Export", trFields.SelectedNode.Text, "false");
             }*/
         }

         private void gbTop_Enter(object sender, EventArgs e)
         {

         }

         private void SelectAll_Click(object sender, EventArgs e)
         {
             aejw.cIni oIni = new aejw.cIni(Application.StartupPath + "\\settings.ini");

             grdColumn.SelectAll();
             for (int i = 0; i < grdColumn.SelectedRows.Count; i++)
             {

                 if (oIni.ReadValue("Collumns2Export", grdColumn.SelectedRows[i].Cells[0].Value.ToString()) == "false")
                 {
                     oIni.WriteValue("Collumns2Export", grdColumn.SelectedRows[i].Cells[0].Value.ToString(), "true");
                     trFields.Nodes.Add(grdColumn.SelectedRows[i].Cells[1].Value.ToString(), grdColumn.SelectedRows[i].Cells[0].Value.ToString());
                 }
             }
        }

         private void DelAllFromExport_Click(object sender, EventArgs e)
         {
             aejw.cIni oIni = new aejw.cIni(Application.StartupPath + "\\settings.ini");

             for (int i = 0; i < trFields.Nodes.Count; ++i)
             {
                 oIni.WriteValue("Collumns2Export", trFields.Nodes[i].Text, "false");
                 //trFields.Nodes.Remove(trFields.Nodes[i]);             
             }
             trFields.Nodes.Clear();
         }

         private void cmbType_SelectedIndexChanged(object sender, EventArgs e)
         {

         }
         private void grdColumn_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
         {
             btnAddTop_Click(null, null);
         }
  
    }
}
